import sqlite3
import time

# 数据库文件会自动生成在根目录
DB_FILE = "guardian_data.db"

class DatabaseManager:
    def __init__(self):
        self.conn = None
        self.init_db()

    def get_connection(self):
        if not self.conn:
            # check_same_thread=False 允许在 Gradio 多线程中使用
            self.conn = sqlite3.connect(DB_FILE, check_same_thread=False)
        return self.conn

    def init_db(self):
        """初始化表结构"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        # 1. 传感器日志表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS sensor_logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                timestamp TEXT,
                sensor_id TEXT,
                status TEXT,
                heart_rate INTEGER,
                temperature REAL,
                is_fall INTEGER
            )
        ''')

        # 2. 对话日志表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS chat_logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                timestamp TEXT,
                role TEXT,
                content TEXT
            )
        ''')
        conn.commit()

    def insert_sensor_data(self, data):
        """插入雷达数据"""
        conn = self.get_connection()
        conn.execute('''
            INSERT INTO sensor_logs (timestamp, sensor_id, status, heart_rate, temperature, is_fall)
            VALUES (?, ?, ?, ?, ?, ?)
        ''', (
            data['timestamp'], 
            data['sensor_id'], 
            data['status'], 
            data['heart_rate'], 
            data['temp'], 
            data['fall_state']
        ))
        conn.commit()

    def insert_chat_log(self, role, content):
        """插入对话日志"""
        conn = self.get_connection()
        timestamp = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        conn.execute('''
            INSERT INTO chat_logs (timestamp, role, content)
            VALUES (?, ?, ?)
        ''', (timestamp, role, content))
        conn.commit()

    def get_recent_sensor_logs(self, limit=5):
        """获取最近几条记录用于展示"""
        conn = self.get_connection()
        cursor = conn.execute('SELECT timestamp, status, heart_rate, temperature FROM sensor_logs ORDER BY id DESC LIMIT ?', (limit,))
        return cursor.fetchall()

# 创建单例对象
db_mgr = DatabaseManager()